/**
 * mongodb数据库配置
 */
const mongoose = require('mongoose')

//连接数据库
mongoose.connect('mongodb://localhost:27017/studentm', {
    useNewUrlParser: true,
    useFindAndModify: false,
    useCreateIndex: true,
    useUnifiedTopology: true
})

//监听数据库连接状态
mongoose.connection.once('open',()=>{
    console.log('数据库连接成功……')
})
mongoose.connection.once('close',()=>{
    console.log('数据库断开……')
})

//创建Schema对象(约束), 将其映射到一个MongoDB collection 并定义这个文档的构成
const Student= mongoose.model('student', new mongoose.Schema({
    snumber: {type: String, unique: true}, // 学号, 具有唯一性
    name: String, // 姓名
    classes: String, // 班级
    sex:  {type: String, default: '女'}, // 性别
    cscore: String, // c语言成绩
    jscore: String, // java成绩
    dbscore: String, // 数据库成绩
    // create_time: { type: Date, default: new Date()}
}))

// console.log(new Date())  // 2021-10-04T13:35:51.535Z
// console.log(Date.now())  // 1633354551540

//录入学生
/*exports.addStudent= async function (req, res) {
    let data= req.body
    const student= await Student.create(data)
    res.send(student)
}*/
exports.addStudent= function (req, res) {
    let data= req.body
    Student.create(data, (err, result)=>{
        if(err){
            res.send({
                code: 201,
                msg: err
            })
        } else{
            res.send({
                code: 200,
                msg: result
            })
        }
    })
}


//删除学生
exports.deleteStudent= function (req, res) {
    let id= req.params.id
    Student.findByIdAndRemove(id, (err, result)=>{
        if(err){
            res.send({
                code: 201,
                msg: err
            })
        } else{
            res.send({
                code: 200,
                msg: result
            })
        }
    })
}

//获取某一个学生的信息
exports.getOneStudent= function (req, res) {
    let id= req.params.id
    Student.findById(id, (err, result)=>{
        if(err){
            res.send({
                code: 201,
                msg: err
            })
        } else{
            res.send({
                code: 200,
                msg: result
            })
        }
    })
}

//修改学生信息
exports.updateStudent= function (req, res) {
    let id= req.params.id
    let data= req.body
    Student.findByIdAndUpdate(id, data,(err, result)=>{
        if(err){
            res.send({
                code: 201,
                msg: err
            })
        } else{
            res.send({
                code: 200,
                msg: result
            })
        }
    })
}

//根据学号进行精确查询
exports.findBySnumber= function (req, res) {
    let snumber= req.params.snumber
    let whereStr= {
        snumber: snumber
    }
    Student.find(whereStr,(err, result)=>{
        if(err){
            res.send({
                code: 201,
                msg: err
            })
        } else{
            res.send({
                code: 200,
                msg: {data: result}
            })
        }
    })
}

//根据姓名进行模糊查询(分页查询)
exports.findByName= function (req, res) {
    /*let whereStr= {
     'name': {$regex: req.query.name}
    }*/
    //姓名 当前页号 每页大小
    let name= req.query.name;
    let currentPage= parseInt(req.query.currentPage);
    let pageSize= parseInt(req.query.pageSize);
    let regExp= new RegExp(name);

    //查询结果
    let studentList={
        data: [],
        total: '',
    }
    Student.find({'name': regExp}).countDocuments({}, function(err, count){
        if(err){
            res.send({
                code: 201,
                msg: err
            })
        } else{
            studentList.total= count;
        }
    })

    Student.find({}, (err,data)=>{
        if(err){
            res.send({
                code: 201,
                msg: err
            })
        } else{
            studentList.data= data;
            res.send({
                code: 200,
                msg: studentList
            })
        }
    }).where({'name': regExp})
        .sort('snumber')
        .skip((currentPage-1)*pageSize)
        .limit(pageSize);
}

//获取学生列表(分页查询)
exports.getStudentList= function (req, res) {
    let currentPage= parseInt(req.query.currentPage);
    let pageSize= parseInt(req.query.pageSize);
    // console.log(currentPage, pageSize)
    //查询结果
    let studentList={
        data: [],
        total: '',
    }
    Student.find({}).countDocuments({}, function(err, count){
        if(err){
            res.send({
                code: 201,
                msg: err
            })
        } else{
            studentList.total= count;
        }
    })

    let query= Student.find({});
    //查询条件
    // query.where({});
    //排序方式  --根据发送时间从小到大排序
    query.sort({'snumber': 1});
    //分页 --跳过条数
    query.skip((currentPage-1)*pageSize);
    //每页多少条
    query.limit(pageSize);
    //查询结果
    query.exec().then(function(e){
        studentList.data= e;
        res.send({
            code: 200,
            msg: studentList
        })
        // console.log(studentList)
    }).catch((err)=>{
        res.send({
            code: 201,
            msg: err
        });
    })
}



// 导出学生列表
const nodeExcel = require('node-xlsx')  // npm install node-xlsx --save
const urlencode = require('urlencode')  // npm install urlencode --save
const config = [  // config 格式
    {
        name: 'snumber',
        title: '学号',
    },
    {
        name: 'name',
        title: '姓名',
    },
    {
        name: 'classes',
        title: '班级',
    },
    {
        name: 'sex',
        title: '性别',
    },
    {
        name: 'cscore',
        title: 'c语言成绩',
    },
    {
        name: 'jscore',
        title: 'java成绩',
    },
    {
        name: 'dbscore',
        title: '数据库成绩',
    },
    /*{
    name: 'create_time',
    title: '录入时间',
    format: function(value) {
            return value.replace(/\.\d+/, '');
        }
    }*/
]

exports.exportStudentListToExcel= function (req, res) {
    let excelName = '学生成绩表'
    let studentList = [];
    studentList.push(config.map(item => {
        return item.title
    }))

    //查询结果
    Student.find({}).then(function(data){
        data.forEach(list=>{
            let student = config.map(item=>{
                const value = list[item.name]
                // return item.format&&item.format(value) || value
                return value
            })
            studentList.push(student)
        })
        // console.log(studentList)

        let buffer = nodeExcel.build([{name: excelName, data: studentList}])
        let name = urlencode(excelName + '_' + (+new Date()) + '.xlsx', "utf-8")  // urlencode：返回字符串，此字符串中除了-_.之外的所有非字母数字字符都将被替换成百分号（%）后跟两位十六进制数，空格则编码为加号（+）
        // res.setHeader('Content-Type', 'application/octet-stream; charset=utf-8')
        res.setHeader('Content-Type', 'application/vnd.ms-excel;charset=utf-8')
        res.setHeader("Access-Control-Expose-Headers", "Content-Disposition")
        res.setHeader("Content-Disposition", "attachment; filename=" + name )  // Content-Disposition 文件名是中文显示

        res.end(buffer, 'binary')

    }).catch((err)=>{
        res.send({
            code: 201,
            msg: err
        });
    })
}

